# -*- coding: utf-8 -*-
# @Author: gengxd
# @Time:   2020-6-24 17:12
# @File:   codeAnalysis

import xlwt
import datetime
import os
from src.db import MysqldbHelper
from conf import settings
import pymysql
from src import mail


class codeAnalysis():
    # 创建一个workbook，并设置编码格式
    workbook = xlwt.Workbook(encoding='utf-8')

    # 日期获取
    today = datetime.date.today()
    reportYear = today.year
    reportMonth = today.month - 1
    reportMonthFirstday = datetime.date(year=today.year,month=reportMonth,day=1)

    # 名称定义
    # 工作簿title定义
    worksheet_title = '科技信息部开发代码量统计' + '(' + str(reportYear) + '年' + str(reportMonth) + '月' + ')'

    # excel文件名称定义
    # workbookName = '科技信息部开发代码量统计(' + str(reportYear) + '年' + str(reportMonth) + '月)' + '.xlsx'
    workbookName = settings.ATTAFILE[0]


    # 字体样式类定义
    class fontStyle:
        def __init__(self,fontName, fontHeight, fontIsBold, fontUnderLine, fontItalic, bgColor,horzPosition,vertPosition,fontColour):
            self.fontName = fontName
            self.fontHeight = fontHeight
            self.fontIsBold = fontIsBold
            self.fontUnderLine = fontUnderLine
            self.fontItalic = fontItalic
            self.bgColor = bgColor
            self.horzPosition = horzPosition
            self.vertPosition = vertPosition
            self.fontColour = fontColour

        def fontCreate(self):
            style = xlwt.XFStyle()  # 初始化样式
            font = xlwt.Font()  # 为样式创建字体
            font.name = self.fontName # 'Microsoft YaHei'  # 设置字体
            font.height = 20 * self.fontHeight  # 字体大小，18为字号，20为衡量单位
            font.bold = self.fontIsBold  # 设置字体黑体
            font.underline = self.fontUnderLine  # 设置字体下划线
            font.italic = self.fontItalic  # 设置字体斜体
            font.colour_index = self.fontColour  # 设置字体颜色
            style.font = font  # 设置样式


            # 设置单元格背景颜色
            pattern = xlwt.Pattern()
            pattern.pattern = xlwt.Pattern.SOLID_PATTERN
            pattern.pattern_fore_colour = self.bgColor
            style.pattern = pattern

            # 设置单元格添加边框
            # 细实线:1，小粗实线:2，细虚线:3，中细虚线:4，大粗实线:5，双线:6，细点虚线:7，大粗虚线:8，细点划线:9，粗点划线:10，细双点划线:11，粗双点划线:12，斜点划线:13
            borders = xlwt.Borders()
            borders.left = 1  # 设置为细实线
            borders.right = 1
            borders.top = 1
            borders.bottom = 1
            borders.left_colour = 0  # 颜色设置   0为黑色 1为白色 2为红色 3为绿色 4为蓝色 5为黄色 6为粉色 7为天蓝
            borders.right_colour = 0
            borders.top_colour = 0
            borders.bottom_colour = 0
            style.borders = borders

            # 设置单元格内容的对齐方式
            alignment = xlwt.Alignment()  ## Create Alignment

            alignment.vert = xlwt.Alignment.VERT_CENTER
            if self.horzPosition == 'HORZ_CENTER':
                alignment.horz = xlwt.Alignment.HORZ_CENTER
            elif self.horzPosition == 'HORZ_LEFT':
                alignment.horz = xlwt.Alignment.HORZ_LEFT
            else:
                alignment.horz = xlwt.Alignment.HORZ_RIGHT
            style.alignment = alignment

            return style


    # 样式参数说明：字体类型,字体大小，字体加粗，字体下划线，字体斜体，单元格背景颜色，水平位置，垂直位置，字体颜色
    title_style = fontStyle('Microsoft YaHei', 18, True, False, False, 40,'HORZ_CENTER','VERT_CENTER', 0)
    theadStyle1 = fontStyle('Microsoft YaHei', 14, True, False, False, 22,'HORZ_CENTER','VERT_CENTER', 0)
    theadStyle2 = fontStyle('Microsoft YaHei', 14, True, False, False, 22,'HORZ_CENTER','VERT_CENTER', 10)
    tbodyStyle1 = fontStyle('Microsoft YaHei', 14, False, False, False, 1,'HORZ_LEFT','VERT_CENTER', 0)
    tbodyStyle2 = fontStyle('Microsoft YaHei', 14, False, False, False, 1,'HORZ_CENTER','VERT_CENTER', 0)
    tbodyStyle3 = fontStyle('Microsoft YaHei', 14, False, False, False, 1,'HORZ_CENTER','VERT_CENTER', 10)

    # 设置样式列表
    theadStyle = [theadStyle1, theadStyle1, theadStyle2, theadStyle1, theadStyle1]
    tbodyStyle = [tbodyStyle1, tbodyStyle2, tbodyStyle3, tbodyStyle2, tbodyStyle2]

    # 设置表格头文本信息
    theadContent = ['姓名','commits','add lines','del lines','total']

    # worksheet名称定义
    worksheetDict = {"inner":"内勤","outter":"外勤",}

    # 创建工作簿
    for sheet in worksheetDict:
        # sheet名称定义
        sheetName = worksheetDict.get(sheet) + '(' + str(reportMonth) + '月份' + ')'

        # 创建worksheet
        worksheet = workbook.add_sheet(sheetName)

        # 设置单元格宽度
        worksheet.col(0).width = 5000

        # 合并单元格,设置单元格样式
        worksheet.write_merge(0,1,0,4, worksheet_title, title_style.fontCreate())

        # 循环设置单元格宽度和表格头样式和内容
        for i in range(5):
            worksheet.col(i).width = 4000

            # 设置表格表头样式
            worksheet.write(2, i, theadContent[i], theadStyle[i].fontCreate())



        # 查询数据库数据
        # 定义数据库连接信息
        config = {
            'host': settings.DBHOST,
            'port': settings.DBPORT,
            'user': settings.DBUSER,
            'passwd': settings.DBPASS,
            'charset': settings.CHARSET,
            'cursorclass': pymysql.cursors.DictCursor
        }

        # 定义查询SQL
        if sheet == 'inner':
            userType = 1
        else:
            userType = 0

        sql = '''SELECT 
                    sec.name,SUM(sec.commit) total_commit,SUM(sec.addition) total_addition,SUM(sec.deletion) total_deletion,SUM(sec.totals) total_all 
                FROM (
                    select 
                        misc_tool.real_email.real_name name,
                        count(misc_tool.git_commit.commit_uid) commit,
                        sum(misc_tool.git_commit.additions) addition,
                        sum(misc_tool.git_commit.deletions) deletion,
                        sum(misc_tool.git_commit.total) totals
                    from misc_tool.git_commit ,misc_tool.real_email
                    where  misc_tool.git_commit.committer_email = misc_tool.real_email.sec_email 
                    and month(commit_datetime)={0} 
                    and commit_datetime > '{1}' 
                    and misc_tool.real_email.type={2}
                    and  misc_tool.git_commit.title 
                    not like 'Merge%%' 
                    and misc_tool.real_email.active = 1
                    GROUP BY misc_tool.real_email.id
                UNION ALL 
                    select 
                        misc_tool.real_email.real_name name,
                        count(misc_tool.git_commit.commit_uid) commit,
                        sum(misc_tool.git_commit.additions) addition,
                        sum(misc_tool.git_commit.deletions) deletion,
                        sum(misc_tool.git_commit.total) totals 
                    from misc_tool.git_commit ,misc_tool.real_email 
                    where  misc_tool.git_commit.committer_email = misc_tool.real_email.email 
                    and month(commit_datetime)={0} 
                    and commit_datetime > '{1}' 
                    and misc_tool.real_email.type={2}
                    and  misc_tool.git_commit.title not like 'Merge%%' 
                    and misc_tool.real_email.active = 1 
                    GROUP BY misc_tool.real_email.id  
                ) sec 
                GROUP BY sec.name
                ORDER BY sec.totals DESC;'''.format(reportMonth, reportMonthFirstday, userType)

        print(sql)

        # 查询数据库中数据，写入worksheet
        # 获取所有记录
        results = MysqldbHelper(config).executeSql(sql)
        print(results,type(results))
        # 循环遍历每行记录，获取每行单元格内容
        rowNum = 1
        for row in results:
            userName = row['name']
            userCommits = row['total_commit']
            userAddlines = row['total_addition']
            userDellines = row['total_deletion']
            userTotal = row['total_all']

            # 创建行列表
            tbodyContent = [userName, userCommits, userAddlines, userDellines, userTotal]

            for i in range(5):
                # 设置表格体样式和内容
                worksheet.write(2 + rowNum, i, tbodyContent[i], tbodyStyle[i].fontCreate())

            rowNum += 1


    # 获取excel文件存储路径
    curDir = settings.ATTAPATH
    print(curDir)
    # 判断文件是否存在，存在删除，重新生成
    if os.path.exists(curDir + '/' + workbookName):
        os.remove(curDir + '/' + workbookName)

    workbook.save(curDir + '/' + workbookName)







